﻿Imports System.Configuration
Imports System.Collections
Imports System.Data
Imports System.Linq
Imports System.Text

Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Imports Tohto.Base.ComUtility
Imports Tohto.Base.CommonConst
Imports Tohto.Base.Entity
Imports Tohto.Base.ExceptionPolicy

Public Class OracleHelp

    Public Shared ReadOnly ConnectionStringLocalTransaction As String = ConfigurationManager.ConnectionStrings("OraConnString1").ConnectionString

    Public Shared Function ExecuteNonQuery(ByVal connectionString As String, ByVal cmdType As CommandType, ByVal cmdText As String, ByRef hasOut As Hashtable, ByVal ParamArray commandParameters As OracleParameter()) As Integer
        Dim cmd As New OracleCommand()

        Using connection As New OracleConnection(connectionString)
            cmd.BindByName = True
            PrepareCommand(cmd, connection, Nothing, cmdType, cmdText, commandParameters)
            Dim val As Integer = cmd.ExecuteNonQuery()
            For i As Integer = 0 To cmd.Parameters.Count - 1
                If cmd.Parameters(i).OracleDbType <> OracleDbType.RefCursor _
                    And (cmd.Parameters(i).Direction = ParameterDirection.Output _
                         OrElse cmd.Parameters(i).Direction = ParameterDirection.InputOutput _
                         OrElse cmd.Parameters(i).Direction = ParameterDirection.ReturnValue) Then
                    hasOut.Add(cmd.Parameters(i).ParameterName, cmd.Parameters(i).Value)
                End If
            Next

            cmd.Parameters.Clear()

            Return val
        End Using
    End Function

    Public Shared Function ExecuteNonQuery(ByVal trans As OracleTransaction, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal ParamArray commandParameters As OracleParameter()) As Integer
        Dim cmd As New OracleCommand()
        cmd.BindByName = True

        PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters)
        Dim val As Integer = cmd.ExecuteNonQuery()
        cmd.Parameters.Clear()

        Return val
    End Function

    Public Shared Function ExecuteNonQuery(ByVal trans As OracleTransaction, ByVal cmdType As CommandType, ByVal cmdText As String, ByRef errInfo As PLSQLErrorInfo, ByVal ParamArray commandParameters As OracleParameter()) As Integer

        Dim cmd As New OracleCommand()
        cmd.BindByName = True

        PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters)
        Dim val As Integer = cmd.ExecuteNonQuery()

        Select Case cmd.Parameters("O_STATS").Value
            Case ComConst.ORA_RET_OK
                errInfo.RETCD = ComConst.ORA_RET_OK
                errInfo.O_VC_ERR_FIELD_NM = String.Empty
                errInfo.O_VC_ERR_MSGCD = String.Empty
                errInfo.O_VC_ERR_MSGPARAMS = String.Empty
                errInfo.O_VC_MESSAGE = String.Empty
            Case ComConst.ORA_RET_NODATA
                errInfo.RETCD = ComConst.ORA_RET_NODATA
                errInfo.O_VC_ERR_FIELD_NM = String.Empty
                errInfo.O_VC_ERR_MSGCD = "CME0040"
                errInfo.O_VC_ERR_MSGPARAMS = String.Empty
                errInfo.O_VC_MESSAGE = String.Empty
            Case ComConst.ORA_RET_EXPIRED
                errInfo.RETCD = ComConst.ORA_RET_EXPIRED
                errInfo.O_VC_ERR_FIELD_NM = String.Empty
                errInfo.O_VC_ERR_MSGCD = "CME0039"
                errInfo.O_VC_ERR_MSGPARAMS = String.Empty
                errInfo.O_VC_MESSAGE = String.Empty
            Case ComConst.ORA_RET_NG
                Dim ref As OracleRefCursor = cmd.Parameters("O_CU_ERR").Value
                Dim dr As OracleDataReader = ref.GetDataReader()
                If dr.Read() Then
                    errInfo.RETCD = ComConst.ORA_RET_NG
                    errInfo.O_VC_ERR_FIELD_NM = Util.ObjToString(dr("ERR_FIELD_NM"))
                    errInfo.O_VC_ERR_MSGCD = Util.ObjToString(dr("ERR_MSGCD"))
                    errInfo.O_VC_ERR_MSGPARAMS = Util.ObjToString(dr("ERR_MSGPARAMS"))
                    errInfo.O_VC_MESSAGE = Util.ObjToString(dr("ERR_MSG"))
                    Throw New OracleLogicException("CME0004")
                End If
            Case ComConst.ORA_RET_CHKERR
                Dim ref As OracleRefCursor = cmd.Parameters("O_CU_ERR").Value
                Dim dr As OracleDataReader = ref.GetDataReader()
                errInfo.RETCD = ComConst.ORA_RET_CHKERR
                If dr.Read() Then
                    errInfo.O_VC_ERR_FIELD_NM = Util.ObjToString(dr("ERR_FIELD_NM"))
                    errInfo.O_VC_ERR_MSGCD = Util.ObjToString(dr("ERR_MSGCD"))
                    errInfo.O_VC_ERR_MSGPARAMS = Util.ObjToString(dr("ERR_MSGPARAMS"))
                    errInfo.O_VC_MESSAGE = Util.ObjToString(dr("ERR_MSG"))
                End If
            Case Else
                Exit Select
        End Select

        cmd.Parameters.Clear()

        Return val
    End Function


    Public Shared Function ExecuteNonQuery(ByVal trans As OracleTransaction, ByVal cmdType As CommandType, ByVal cmdText As String, ByRef errInfo As PLSQLErrorInfo, ByRef hasOut As Hashtable, ByVal ParamArray commandParameters As OracleParameter()) As Integer

        Dim cmd As New OracleCommand()
        cmd.BindByName = True

        PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters)
        Dim val As Integer = cmd.ExecuteNonQuery()

        Select Case cmd.Parameters("O_STATS").Value
            Case ComConst.ORA_RET_OK
                errInfo.RETCD = ComConst.ORA_RET_OK
                errInfo.O_VC_ERR_FIELD_NM = String.Empty
                errInfo.O_VC_ERR_MSGCD = String.Empty
                errInfo.O_VC_ERR_MSGPARAMS = String.Empty
                errInfo.O_VC_MESSAGE = String.Empty

                For i As Integer = 0 To cmd.Parameters.Count - 1
                    If cmd.Parameters(i).OracleDbType <> OracleDbType.RefCursor _
                        And (cmd.Parameters(i).Direction = ParameterDirection.Output _
                             OrElse cmd.Parameters(i).Direction = ParameterDirection.InputOutput _
                             OrElse cmd.Parameters(i).Direction = ParameterDirection.ReturnValue) Then
                        hasOut.Add(cmd.Parameters(i).ParameterName, cmd.Parameters(i).Value)
                    End If
                Next
            Case ComConst.ORA_RET_NODATA
                errInfo.RETCD = ComConst.ORA_RET_NODATA
                errInfo.O_VC_ERR_FIELD_NM = String.Empty
                errInfo.O_VC_ERR_MSGCD = "CME0040"
                errInfo.O_VC_ERR_MSGPARAMS = String.Empty
                errInfo.O_VC_MESSAGE = String.Empty
            Case ComConst.ORA_RET_EXPIRED
                errInfo.RETCD = ComConst.ORA_RET_EXPIRED
                errInfo.O_VC_ERR_FIELD_NM = String.Empty
                errInfo.O_VC_ERR_MSGCD = "CME0039"
                errInfo.O_VC_ERR_MSGPARAMS = String.Empty
                errInfo.O_VC_MESSAGE = String.Empty
            Case ComConst.ORA_RET_NG
                Dim ref As OracleRefCursor = cmd.Parameters("O_CU_ERR").Value
                Dim dr As OracleDataReader = ref.GetDataReader()
                If dr.Read() Then
                    errInfo.RETCD = ComConst.ORA_RET_NG
                    errInfo.O_VC_ERR_FIELD_NM = Util.ObjToString(dr("ERR_FIELD_NM"))
                    errInfo.O_VC_ERR_MSGCD = Util.ObjToString(dr("ERR_MSGCD"))
                    errInfo.O_VC_ERR_MSGPARAMS = Util.ObjToString(dr("ERR_MSGPARAMS"))
                    errInfo.O_VC_MESSAGE = Util.ObjToString(dr("ERR_MSG"))
                    Throw New OracleLogicException("CME0004")
                End If
            Case ComConst.ORA_RET_CHKERR
                Dim ref As OracleRefCursor = cmd.Parameters("O_CU_ERR").Value
                Dim dr As OracleDataReader = ref.GetDataReader()
                errInfo.RETCD = ComConst.ORA_RET_CHKERR
                If dr.Read() Then
                    errInfo.O_VC_ERR_FIELD_NM = Util.ObjToString(dr("ERR_FIELD_NM"))
                    errInfo.O_VC_ERR_MSGCD = Util.ObjToString(dr("ERR_MSGCD"))
                    errInfo.O_VC_ERR_MSGPARAMS = Util.ObjToString(dr("ERR_MSGPARAMS"))
                    errInfo.O_VC_MESSAGE = Util.ObjToString(dr("ERR_MSG"))
                End If
            Case Else
                Exit Select
        End Select

        cmd.Parameters.Clear()

        Return val
    End Function

    Public Shared Function ExecuteNonQuery(ByVal connection As OracleConnection, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal ParamArray commandParameters As OracleParameter()) As Integer

        Dim cmd As New OracleCommand()

        PrepareCommand(cmd, connection, Nothing, cmdType, cmdText, commandParameters)
        Dim val As Integer = cmd.ExecuteNonQuery()
        cmd.Parameters.Clear()

        Return val
    End Function

    Public Shared Function FillTable(ByVal trans As OracleTransaction, ByVal cmdType As CommandType, ByVal cmdText As String, ByRef errInfo As PLSQLErrorInfo, ByVal commandParameters As OracleParameter(), Optional ByVal tableName As String() = Nothing) As DataSet
        Dim cmd As New OracleCommand()
        Dim adp As New OracleDataAdapter()

        Using trans.Connection
            Dim parOutTable As New OracleParameter
            Dim dt As New DataTable
            Dim ds As New DataSet

            cmd.BindByName = True

            PrepareTableCommand(cmd, trans.Connection, Nothing, cmdType, cmdText, commandParameters)
            adp.SelectCommand = cmd

            If Not IsNothing(tableName) Then
                For i As Integer = 0 To tableName.Count - 1
                    If i = 0 Then
                        adp.TableMappings.Add("Table", tableName(i))
                    Else
                        adp.TableMappings.Add("Table" & i, tableName(i))
                    End If
                Next
            End If

            adp.Fill(ds)

            Select Case cmd.Parameters("O_STATS").Value
                Case ComConst.ORA_RET_OK
                    errInfo.RETCD = ComConst.ORA_RET_OK
                    errInfo.O_VC_ERR_FIELD_NM = String.Empty
                    errInfo.O_VC_ERR_MSGCD = String.Empty
                    errInfo.O_VC_ERR_MSGPARAMS = String.Empty
                    errInfo.O_VC_MESSAGE = String.Empty
                Case ComConst.ORA_RET_NODATA
                    errInfo.RETCD = ComConst.ORA_RET_NODATA
                    errInfo.O_VC_ERR_FIELD_NM = String.Empty
                    errInfo.O_VC_ERR_MSGCD = "CME0040"
                    errInfo.O_VC_ERR_MSGPARAMS = String.Empty
                    errInfo.O_VC_MESSAGE = String.Empty
                Case ComConst.ORA_RET_EXPIRED
                    errInfo.RETCD = ComConst.ORA_RET_EXPIRED
                    errInfo.O_VC_ERR_FIELD_NM = String.Empty
                    errInfo.O_VC_ERR_MSGCD = "CME0039"
                    errInfo.O_VC_ERR_MSGPARAMS = String.Empty
                    errInfo.O_VC_MESSAGE = String.Empty
                Case ComConst.ORA_RET_NG
                    If ds.Tables(ds.Tables.Count - 1).Rows.Count > 0 Then
                        errInfo.RETCD = ComConst.ORA_RET_NG
                        Dim tblIndex As Integer = ds.Tables.Count - 1
                        errInfo.O_VC_ERR_FIELD_NM = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_FIELD_NM"))
                        errInfo.O_VC_ERR_MSGCD = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_MSGCD"))
                        errInfo.O_VC_ERR_MSGPARAMS = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_MSGPARAMS"))
                        errInfo.O_VC_MESSAGE = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_MSG"))
                        Throw New OracleLogicException("CME0004")
                    End If
                Case ComConst.ORA_RET_CHKERR
                    errInfo.RETCD = ComConst.ORA_RET_CHKERR
                    Dim tblIndex As Integer = ds.Tables.Count - 1
                    errInfo.O_VC_ERR_FIELD_NM = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_FIELD_NM"))
                    errInfo.O_VC_ERR_MSGCD = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_MSGCD"))
                    errInfo.O_VC_ERR_MSGPARAMS = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_MSGPARAMS"))
                    errInfo.O_VC_MESSAGE = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_MSG"))
                Case Else
                    Exit Select
            End Select

            cmd.Parameters.Clear()

            Return ds
        End Using
    End Function

    Public Shared Function FillTable(ByVal trans As OracleTransaction, ByVal cmdType As CommandType, ByVal cmdText As String, ByRef errInfo As PLSQLErrorInfo, ByVal commandParameters As OracleParameter(), ByRef hasOut As Hashtable, Optional ByVal tableName As String() = Nothing) As DataSet
        Dim cmd As New OracleCommand()
        Dim adp As New OracleDataAdapter()

        Using trans.Connection
            Dim parOutTable As New OracleParameter
            Dim dt As New DataTable
            Dim ds As New DataSet

            cmd.BindByName = True

            PrepareTableCommand(cmd, trans.Connection, Nothing, cmdType, cmdText, commandParameters)
            adp.SelectCommand = cmd

            If Not IsNothing(tableName) Then
                For i As Integer = 0 To tableName.Count - 1
                    If i = 0 Then
                        adp.TableMappings.Add("Table", tableName(i))
                    Else
                        adp.TableMappings.Add("Table" & i, tableName(i))
                    End If
                Next
            End If

            adp.Fill(ds)

            Select Case cmd.Parameters("O_STATS").Value
                Case ComConst.ORA_RET_OK
                    errInfo.RETCD = ComConst.ORA_RET_OK
                    errInfo.O_VC_ERR_FIELD_NM = String.Empty
                    errInfo.O_VC_ERR_MSGCD = String.Empty
                    errInfo.O_VC_ERR_MSGPARAMS = String.Empty
                    errInfo.O_VC_MESSAGE = String.Empty

                    For i As Integer = 0 To cmd.Parameters.Count - 1
                        If cmd.Parameters(i).OracleDbType <> OracleDbType.RefCursor _
                            And (cmd.Parameters(i).Direction = ParameterDirection.Output _
                                 OrElse cmd.Parameters(i).Direction = ParameterDirection.InputOutput) Then
                            hasOut.Add(cmd.Parameters(i).ParameterName, cmd.Parameters(i).Value)
                        End If
                    Next
                Case ComConst.ORA_RET_NODATA
                    errInfo.RETCD = ComConst.ORA_RET_NODATA
                    errInfo.O_VC_ERR_FIELD_NM = String.Empty
                    errInfo.O_VC_ERR_MSGCD = "CME0040"
                    errInfo.O_VC_ERR_MSGPARAMS = String.Empty
                    errInfo.O_VC_MESSAGE = String.Empty
                Case ComConst.ORA_RET_EXPIRED
                    errInfo.RETCD = ComConst.ORA_RET_EXPIRED
                    errInfo.O_VC_ERR_FIELD_NM = String.Empty
                    errInfo.O_VC_ERR_MSGCD = "CME0039"
                    errInfo.O_VC_ERR_MSGPARAMS = String.Empty
                    errInfo.O_VC_MESSAGE = String.Empty
                Case ComConst.ORA_RET_NG
                    If ds.Tables(ds.Tables.Count - 1).Rows.Count > 0 Then
                        errInfo.RETCD = ComConst.ORA_RET_NG
                        Dim tblIndex As Integer = ds.Tables.Count - 1
                        errInfo.O_VC_ERR_FIELD_NM = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_FIELD_NM"))
                        errInfo.O_VC_ERR_MSGCD = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_MSGCD"))
                        errInfo.O_VC_ERR_MSGPARAMS = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_MSGPARAMS"))
                        errInfo.O_VC_MESSAGE = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_MSG"))
                        Throw New OracleLogicException("CME0004")
                    End If
                Case ComConst.ORA_RET_CHKERR
                    errInfo.RETCD = ComConst.ORA_RET_CHKERR
                    Dim tblIndex As Integer = ds.Tables.Count - 1
                    errInfo.O_VC_ERR_FIELD_NM = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_FIELD_NM"))
                    errInfo.O_VC_ERR_MSGCD = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_MSGCD"))
                    errInfo.O_VC_ERR_MSGPARAMS = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_MSGPARAMS"))
                    errInfo.O_VC_MESSAGE = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_MSG"))
                Case Else
                    Exit Select
            End Select

            cmd.Parameters.Clear()

            Return ds
        End Using
    End Function

    Public Shared Function FillTable(ByVal connectionString As String, ByVal cmdType As CommandType, ByVal cmdText As String, ByRef errInfo As PLSQLErrorInfo, ByVal commandParameters As OracleParameter(), Optional ByVal tableName As String() = Nothing) As DataSet
        Dim cmd As New OracleCommand()
        Dim adp As New OracleDataAdapter()

        Using connection As New OracleConnection(connectionString)
            Dim parOutTable As New OracleParameter
            Dim dt As New DataTable
            Dim ds As New DataSet

            cmd.BindByName = True

            PrepareTableCommand(cmd, connection, Nothing, cmdType, cmdText, commandParameters)
            adp.SelectCommand = cmd

            If Not IsNothing(tableName) Then
                For i As Integer = 0 To tableName.Count - 1
                    If i = 0 Then
                        adp.TableMappings.Add("Table", tableName(i))
                    Else
                        adp.TableMappings.Add("Table" & i, tableName(i))
                    End If
                Next
            End If

            adp.Fill(ds)

            Select Case cmd.Parameters("O_STATS").Value
                Case ComConst.ORA_RET_OK
                    errInfo.RETCD = ComConst.ORA_RET_OK
                    errInfo.O_VC_ERR_FIELD_NM = String.Empty
                    errInfo.O_VC_ERR_MSGCD = String.Empty
                    errInfo.O_VC_ERR_MSGPARAMS = String.Empty
                    errInfo.O_VC_MESSAGE = String.Empty
                Case ComConst.ORA_RET_NODATA
                    errInfo.RETCD = ComConst.ORA_RET_NODATA
                    errInfo.O_VC_ERR_FIELD_NM = String.Empty
                    errInfo.O_VC_ERR_MSGCD = "CME0040"
                    errInfo.O_VC_ERR_MSGPARAMS = String.Empty
                    errInfo.O_VC_MESSAGE = String.Empty
                Case ComConst.ORA_RET_EXPIRED
                    errInfo.RETCD = ComConst.ORA_RET_EXPIRED
                    errInfo.O_VC_ERR_FIELD_NM = String.Empty
                    errInfo.O_VC_ERR_MSGCD = "CME0039"
                    errInfo.O_VC_ERR_MSGPARAMS = String.Empty
                    errInfo.O_VC_MESSAGE = String.Empty
                Case ComConst.ORA_RET_NG
                    If ds.Tables(ds.Tables.Count - 1).Rows.Count > 0 Then
                        errInfo.RETCD = ComConst.ORA_RET_NG
                        Dim tblIndex As Integer = ds.Tables.Count - 1
                        errInfo.O_VC_ERR_FIELD_NM = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_FIELD_NM"))
                        errInfo.O_VC_ERR_MSGCD = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_MSGCD"))
                        errInfo.O_VC_ERR_MSGPARAMS = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_MSGPARAMS"))
                        errInfo.O_VC_MESSAGE = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_MSG"))
                        Throw New OracleLogicException("CME0004")
                    End If
                Case ComConst.ORA_RET_CHKERR
                    errInfo.RETCD = ComConst.ORA_RET_CHKERR
                    Dim tblIndex As Integer = ds.Tables.Count - 1
                    errInfo.O_VC_ERR_FIELD_NM = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_FIELD_NM"))
                    errInfo.O_VC_ERR_MSGCD = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_MSGCD"))
                    errInfo.O_VC_ERR_MSGPARAMS = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_MSGPARAMS"))
                    errInfo.O_VC_MESSAGE = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_MSG"))
                Case Else
                    Exit Select
            End Select

            cmd.Parameters.Clear()

            Return ds
        End Using
    End Function

    Public Shared Function FillTable(ByVal connectionString As String, ByVal cmdType As CommandType, ByVal cmdText As String, ByRef errInfo As PLSQLErrorInfo, ByVal commandParameters As OracleParameter(), ByRef hasOut As Hashtable, Optional ByVal tableName As String() = Nothing) As DataSet
        Dim cmd As New OracleCommand()
        Dim adp As New OracleDataAdapter()

        Using connection As New OracleConnection(connectionString)
            Dim parOutTable As New OracleParameter
            Dim dt As New DataTable
            Dim ds As New DataSet

            cmd.BindByName = True

            PrepareTableCommand(cmd, connection, Nothing, cmdType, cmdText, commandParameters)
            adp.SelectCommand = cmd

            If Not IsNothing(tableName) Then
                For i As Integer = 0 To tableName.Count - 1
                    If i = 0 Then
                        adp.TableMappings.Add("Table", tableName(i))
                    Else
                        adp.TableMappings.Add("Table" & i, tableName(i))
                    End If
                Next
            End If

            adp.Fill(ds)

            Select Case cmd.Parameters("O_STATS").Value
                Case ComConst.ORA_RET_OK
                    errInfo.RETCD = ComConst.ORA_RET_OK
                    errInfo.O_VC_ERR_FIELD_NM = String.Empty
                    errInfo.O_VC_ERR_MSGCD = String.Empty
                    errInfo.O_VC_ERR_MSGPARAMS = String.Empty
                    errInfo.O_VC_MESSAGE = String.Empty

                    For i As Integer = 0 To cmd.Parameters.Count - 1
                        If cmd.Parameters(i).OracleDbType <> OracleDbType.RefCursor _
                            And (cmd.Parameters(i).Direction = ParameterDirection.Output _
                                 OrElse cmd.Parameters(i).Direction = ParameterDirection.InputOutput) Then
                            hasOut.Add(cmd.Parameters(i).ParameterName, cmd.Parameters(i).Value)
                        End If
                    Next
                Case ComConst.ORA_RET_NODATA
                    errInfo.RETCD = ComConst.ORA_RET_NODATA
                    errInfo.O_VC_ERR_FIELD_NM = String.Empty
                    errInfo.O_VC_ERR_MSGCD = "CME0040"
                    errInfo.O_VC_ERR_MSGPARAMS = String.Empty
                    errInfo.O_VC_MESSAGE = String.Empty
                Case ComConst.ORA_RET_EXPIRED
                    errInfo.RETCD = ComConst.ORA_RET_EXPIRED
                    errInfo.O_VC_ERR_FIELD_NM = String.Empty
                    errInfo.O_VC_ERR_MSGCD = "CME0039"
                    errInfo.O_VC_ERR_MSGPARAMS = String.Empty
                    errInfo.O_VC_MESSAGE = String.Empty
                Case ComConst.ORA_RET_NG
                    If ds.Tables(ds.Tables.Count - 1).Rows.Count > 0 Then
                        errInfo.RETCD = ComConst.ORA_RET_NG
                        Dim tblIndex As Integer = ds.Tables.Count - 1
                        errInfo.O_VC_ERR_FIELD_NM = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_FIELD_NM"))
                        errInfo.O_VC_ERR_MSGCD = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_MSGCD"))
                        errInfo.O_VC_ERR_MSGPARAMS = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_MSGPARAMS"))
                        errInfo.O_VC_MESSAGE = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_MSG"))
                        Throw New OracleLogicException("CME0004")
                    End If
                Case ComConst.ORA_RET_CHKERR
                    errInfo.RETCD = ComConst.ORA_RET_CHKERR
                    Dim tblIndex As Integer = ds.Tables.Count - 1
                    errInfo.O_VC_ERR_FIELD_NM = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_FIELD_NM"))
                    errInfo.O_VC_ERR_MSGCD = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_MSGCD"))
                    errInfo.O_VC_ERR_MSGPARAMS = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_MSGPARAMS"))
                    errInfo.O_VC_MESSAGE = Util.ObjToString(ds.Tables(tblIndex).Rows(0)("ERR_MSG"))
                Case Else
                    Exit Select
            End Select

            cmd.Parameters.Clear()

            Return ds
        End Using
    End Function

    Public Shared Function ExecuteReader(ByVal connectionString As String, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal ParamArray commandParameters As OracleParameter()) As OracleDataReader

        Dim cmd As New OracleCommand()
        Dim conn As New OracleConnection(connectionString)

        Try
            PrepareCommand(cmd, conn, Nothing, cmdType, cmdText, commandParameters)
            Dim rdr As OracleDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            cmd.Parameters.Clear()

            Return rdr
        Catch
            conn.Close()
            Throw
        End Try
    End Function

    Public Shared Function ExecuteScalar(ByVal connectionString As String, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal ParamArray commandParameters As OracleParameter()) As Object
        Dim cmd As New OracleCommand()

        Using conn As New OracleConnection(connectionString)
            PrepareCommand(cmd, conn, Nothing, cmdType, cmdText, commandParameters)
            Dim val As Object = cmd.ExecuteScalar()
            cmd.Parameters.Clear()
            Return val
        End Using
    End Function

    Public Shared Function ExecuteScalar(ByVal transaction As OracleTransaction, ByVal commandType As CommandType, ByVal commandText As String, ByVal ParamArray commandParameters As OracleParameter()) As Object
        If transaction Is Nothing Then
            Throw New ArgumentNullException("transaction")
        End If
        If transaction IsNot Nothing AndAlso transaction.Connection Is Nothing Then
            Throw New ArgumentException("The transaction was rollbacked" & vbTab & "or commited, please" & vbTab & "provide" & vbTab & "an open" & vbTab & "transaction.", "transaction")
        End If

        Dim cmd As New OracleCommand()

        PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters)

        Dim retval As Object = cmd.ExecuteScalar()

        cmd.Parameters.Clear()
        Return retval
    End Function

    Public Shared Function ExecuteScalar(ByVal connectionString As OracleConnection, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal ParamArray commandParameters As OracleParameter()) As Object
        Dim cmd As New OracleCommand()

        PrepareCommand(cmd, connectionString, Nothing, cmdType, cmdText, commandParameters)
        Dim val As Object = cmd.ExecuteScalar()
        cmd.Parameters.Clear()
        Return val
    End Function


    Private Shared Sub PrepareCommand(ByVal cmd As OracleCommand, ByVal conn As OracleConnection, ByVal trans As OracleTransaction, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal commandParameters As OracleParameter())

        If conn.State <> ConnectionState.Open Then
            conn.Open()
        End If

        cmd.Connection = conn
        cmd.CommandText = cmdText
        cmd.CommandType = cmdType

        If commandParameters IsNot Nothing Then
            For Each parm As OracleParameter In commandParameters
                cmd.Parameters.Add(parm)
            Next
        End If
    End Sub

    Private Shared Sub PrepareTableCommand(ByVal cmd As OracleCommand, ByVal conn As OracleConnection, ByVal trans As OracleTransaction, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal commandParameters As OracleParameter())

        If conn.State <> ConnectionState.Open Then
            conn.Open()
        End If

        cmd.Connection = conn
        cmd.CommandText = cmdText
        cmd.CommandType = cmdType

        If commandParameters IsNot Nothing Then
            For Each parm As OracleParameter In commandParameters
                cmd.Parameters.Add(parm)
            Next
        End If
    End Sub

    Public Shared Function OraBit(ByVal value As Boolean) As String
        If value Then
            Return "Y"
        Else
            Return "N"
        End If
    End Function

    Public Shared Function OraBool(ByVal value As String) As Boolean
        If value.Equals("Y") Then
            Return True
        Else
            Return False
        End If
    End Function
End Class
